{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ec698951",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   target  source\n",
      "0       /api/123-shop-pro       1\n",
      "1     /api/123contactform       1\n",
      "2        /api/12secondstv       1\n",
      "3          /api/140-proof       1\n",
      "4            /api/18amail       1\n",
      "...                   ...     ...\n",
      "1503          /api/zooomr       4\n",
      "1504          /api/zoopla       2\n",
      "1505         /api/zootool       1\n",
      "1506          /api/zotero       2\n",
      "1507          /api/zvents       7\n",
      "\n",
      "[1508 rows x 2 columns]\n",
      "        tp                          url  \\\n",
      "0      api       /api/pinnaclecart-rest   \n",
      "1      api  /api/volusion-shopping-cart   \n",
      "2      api                 /api/untappd   \n",
      "3      api                  /api/envato   \n",
      "4      api        /api/edrv-ev-charging   \n",
      "...    ...                          ...   \n",
      "23574  api            /api/dropbox-sync   \n",
      "23575  api                  /api/kratia   \n",
      "23576  api             /api/messagemost   \n",
      "23577  api               /api/sec-watch   \n",
      "23578  api               /api/answerbag   \n",
      "\n",
      "                                           name          st          et  \\\n",
      "0           PinnacleCart REST API MASTER RECORD  2011-01-25  2014-11-04   \n",
      "1      Volusion Shopping Cart API MASTER RECORD  2012-06-14  2014-09-14   \n",
      "2                     Untappd API MASTER RECORD  2010-12-21  2222-02-22   \n",
      "3                      Envato API MASTER RECORD  2011-03-02  2019-06-05   \n",
      "4            eDRV EV Charging API MASTER RECORD  2020-07-29  2020-07-31   \n",
      "...                                         ...         ...         ...   \n",
      "23574            Dropbox Sync API MASTER RECORD  2014-09-21  2017-01-17   \n",
      "23575                  Kratia API MASTER RECORD  2006-02-03  2012-08-23   \n",
      "23576             MessageMost API MASTER RECORD  2009-12-23  2016-09-30   \n",
      "23577               SEC Watch API MASTER RECORD  2009-01-27  2014-02-10   \n",
      "23578               Answerbag API MASTER RECORD  2007-02-15  2013-05-26   \n",
      "\n",
      "              oet          c    oac     ac  \n",
      "0      2222-02-22  eCommerce   True  False  \n",
      "1      2222-02-22  eCommerce   True  False  \n",
      "2      2222-02-22       Food   True   True  \n",
      "3      2222-02-22  eCommerce   True  False  \n",
      "4      2222-02-22     Energy   True  False  \n",
      "...           ...        ...    ...    ...  \n",
      "23574  2015-10-07    Syncing  False  False  \n",
      "23575  2014-04-19     Search  False  False  \n",
      "23576  2014-04-19     Social  False  False  \n",
      "23577  2014-04-19  Financial  False  False  \n",
      "23578  2014-04-19        Q&A  False  False  \n",
      "\n",
      "[23579 rows x 9 columns]\n",
      "                               url          c\n",
      "0           /api/pinnaclecart-rest  eCommerce\n",
      "1      /api/volusion-shopping-cart  eCommerce\n",
      "2                     /api/untappd       Food\n",
      "3                      /api/envato  eCommerce\n",
      "4            /api/edrv-ev-charging     Energy\n",
      "...                            ...        ...\n",
      "23574            /api/dropbox-sync    Syncing\n",
      "23575                  /api/kratia     Search\n",
      "23576             /api/messagemost     Social\n",
      "23577               /api/sec-watch  Financial\n",
      "23578               /api/answerbag        Q&A\n",
      "\n",
      "[23579 rows x 2 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\l\\AppData\\Local\\Temp\\ipykernel_32792\\4264692610.py:3: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  data_edges=pd.read_csv(m_a_edges, encoding=\"utf-8\",sep='\\t', error_bad_lines=False)\n",
      "C:\\Users\\l\\AppData\\Local\\Temp\\ipykernel_32792\\4264692610.py:8: FutureWarning: The error_bad_lines argument has been deprecated and will be removed in a future version. Use on_bad_lines in the future.\n",
      "\n",
      "\n",
      "  data_api=pd.read_csv(api_nodes_estimator, encoding=\"utf-8\",sep='\\t',error_bad_lines=False)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "m_a_edges=\"m-a_edges.csv\"\n",
    "data_edges=pd.read_csv(m_a_edges, encoding=\"utf-8\",sep='\\t', error_bad_lines=False)\n",
    "api_usetimes=data_edges.groupby(\"target\").count().reset_index()\n",
    "print(api_usetimes)\n",
    "\n",
    "api_nodes_estimator=\"api_nodes_estimator.csv\"\n",
    "data_api=pd.read_csv(api_nodes_estimator, encoding=\"utf-8\",sep='\\t',error_bad_lines=False)\n",
    "print(data_api)\n",
    "\n",
    "data_api_3=data_api.loc[:,['url','c']]\n",
    "print(data_api_3)\n",
    "\n",
    "# data_api_3.set_index([\"url\"], inplace=True)\n",
    "# data_api_3=data_api.loc[:,['url','c']]\n",
    "# print(data_api_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "43007010",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>target</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/api/123-shop-pro</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/api/123contactform</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/api/12secondstv</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/api/140-proof</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/api/18amail</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1503</th>\n",
       "      <td>/api/zooomr</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1504</th>\n",
       "      <td>/api/zoopla</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1505</th>\n",
       "      <td>/api/zootool</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1506</th>\n",
       "      <td>/api/zotero</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1507</th>\n",
       "      <td>/api/zvents</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1508 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   target  source\n",
       "0       /api/123-shop-pro       1\n",
       "1     /api/123contactform       1\n",
       "2        /api/12secondstv       1\n",
       "3          /api/140-proof       1\n",
       "4            /api/18amail       1\n",
       "...                   ...     ...\n",
       "1503          /api/zooomr       4\n",
       "1504          /api/zoopla       2\n",
       "1505         /api/zootool       1\n",
       "1506          /api/zotero       2\n",
       "1507          /api/zvents       7\n",
       "\n",
       "[1508 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "api_usetimes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "61709708",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>url</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/api/pinnaclecart-rest</th>\n",
       "      <td>eCommerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/volusion-shopping-cart</th>\n",
       "      <td>eCommerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/untappd</th>\n",
       "      <td>Food</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/envato</th>\n",
       "      <td>eCommerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/edrv-ev-charging</th>\n",
       "      <td>Energy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/dropbox-sync</th>\n",
       "      <td>Syncing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/kratia</th>\n",
       "      <td>Search</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/messagemost</th>\n",
       "      <td>Social</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/sec-watch</th>\n",
       "      <td>Financial</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>/api/answerbag</th>\n",
       "      <td>Q&amp;A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23579 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     c\n",
       "url                                   \n",
       "/api/pinnaclecart-rest       eCommerce\n",
       "/api/volusion-shopping-cart  eCommerce\n",
       "/api/untappd                      Food\n",
       "/api/envato                  eCommerce\n",
       "/api/edrv-ev-charging           Energy\n",
       "...                                ...\n",
       "/api/dropbox-sync              Syncing\n",
       "/api/kratia                     Search\n",
       "/api/messagemost                Social\n",
       "/api/sec-watch               Financial\n",
       "/api/answerbag                     Q&A\n",
       "\n",
       "[23579 rows x 1 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_api_3\n",
    "data_api_3.set_index([\"url\"], inplace=True)\n",
    "data_api_3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "9582a1d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   target  source     category\n",
      "0       /api/123-shop-pro       1    eCommerce\n",
      "1     /api/123contactform       1        Tools\n",
      "2        /api/12secondstv       1        Video\n",
      "3          /api/140-proof       1  Advertising\n",
      "4            /api/18amail       1        Email\n",
      "...                   ...     ...          ...\n",
      "1503          /api/zooomr       4       Photos\n",
      "1504          /api/zoopla       2  Real Estate\n",
      "1505         /api/zootool       1    Bookmarks\n",
      "1506          /api/zotero       2    Bookmarks\n",
      "1507          /api/zvents       7       Events\n",
      "\n",
      "[1508 rows x 3 columns]\n",
      "                     source     category\n",
      "target                                  \n",
      "/api/123-shop-pro         1    eCommerce\n",
      "/api/123contactform       1        Tools\n",
      "/api/12secondstv          1        Video\n",
      "/api/140-proof            1  Advertising\n",
      "/api/18amail              1        Email\n",
      "...                     ...          ...\n",
      "/api/zooomr               4       Photos\n",
      "/api/zoopla               2  Real Estate\n",
      "/api/zootool              1    Bookmarks\n",
      "/api/zotero               2    Bookmarks\n",
      "/api/zvents               7       Events\n",
      "\n",
      "[1508 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "category=[]\n",
    "for i in range(len(api_usetimes)):\n",
    "    url=api_usetimes.iloc[i,0]\n",
    "    if url in data_api_3.index:\n",
    "        c=data_api_3.loc[url,'c']        \n",
    "    else:\n",
    "        c=\"nan\"       \n",
    "    category.append(c)\n",
    "    \n",
    "api_usetimes['category']=category\n",
    "\n",
    "print(api_usetimes)\n",
    "        \n",
    "api_usetimes.set_index([\"target\"], inplace=True)   \n",
    "print(api_usetimes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "3f159493",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.io.formats.printing.PrettyDict'>\n",
      "<class 'pandas.io.formats.printing.PrettyDict'>\n",
      "(('Mapping', 1984), Index(['/api/google-maps'], dtype='object', name='target'))\n",
      "(('Social', 671), Index(['/api/twitter'], dtype='object', name='target'))\n",
      "(('Video', 562), Index(['/api/youtube'], dtype='object', name='target'))\n",
      "(('Photos', 474), Index(['/api/flickr'], dtype='object', name='target'))\n",
      "(('Social', 381), Index(['/api/facebook'], dtype='object', name='target'))\n",
      "(('Telephony', 311), Index(['/api/twilio'], dtype='object', name='target'))\n",
      "(('eCommerce', 304), Index(['/api/amazon-product-advertising'], dtype='object', name='target'))\n",
      "(('Music', 181), Index(['/api/lastfm'], dtype='object', name='target'))\n",
      "(('Search', 174), Index(['/api/ebay'], dtype='object', name='target'))\n",
      "(('Messaging', 156), Index(['/api/twilio-sms'], dtype='object', name='target'))\n",
      "(('Search', 139), Index(['/api/google-search'], dtype='object', name='target'))\n",
      "(('Mapping', 117), Index(['/api/microsoft-bing-maps'], dtype='object', name='target'))\n",
      "(('Bookmarks', 106), Index(['/api/delicious-0'], dtype='object', name='target'))\n",
      "(('Social', 104), Index(['/api/foursquare'], dtype='object', name='target'))\n",
      "(('Tools', 102), Index(['/api/google-app-engine'], dtype='object', name='target'))\n",
      "(('Electronic Signature', 97), Index(['/api/docusign-enterprise'], dtype='object', name='target'))\n",
      "(('Search', 95), Index(['/api/yahoo-search'], dtype='object', name='target'))\n",
      "(('Widgets', 95), Index(['/api/google-gadgets'], dtype='object', name='target'))\n",
      "(('Messaging', 93), Index(['/api/411sync'], dtype='object', name='target'))\n",
      "(('Other', 84), Index(['/api/google-chart'], dtype='object', name='target'))\n"
     ]
    }
   ],
   "source": [
    "df=api_usetimes.groupby(['category','source'],as_index=False)\n",
    "print(type(df.groups))\n",
    "# print(df.groups)\n",
    "#转化为字典形式，按照key中的数值降序排序\n",
    "df1=df.groups\n",
    "print(type(df1))\n",
    "# new_res=sorted(res.items(),key=lambda item:-item[1])\n",
    "df1=sorted(df1.items(),key=lambda item:-item[0][1])\n",
    "# print(df1[:10])\n",
    "for i in range(20):\n",
    "    print(df1[i])\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "bf0b2077",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/api/geocoder\n",
      "/api/amazon-ec2\n",
      "/api/panoramio\n",
      "/api/yahoo-geocoding\n",
      "/api/yelp-fusion\n",
      "/api/google-maps\n",
      "/api/amazon-product-advertising\n",
      "/api/lastfm\n",
      "/api/geonames\n",
      "/api/ebay\n",
      "/api/google-adsense\n",
      "/api/google-earth\n",
      "/api/twilio\n",
      "/api/linkedin\n",
      "/api/google-chart\n",
      "/api/amazon-s3\n",
      "/api/wikipedia\n",
      "/api/facebook\n",
      "/api/twilio-sms\n",
      "/api/google-search\n",
      "/api/twitter\n",
      "/api/flickr\n",
      "/api/technorati\n",
      "/api/youtube\n",
      "/api/foursquare\n",
      "/api/delicious-0\n",
      "/api/google-maps-data\n",
      "/api/yahoo-search\n"
     ]
    }
   ],
   "source": [
    "# import re\n",
    "pairs = open(\"all_pairs.txt\",'r',encoding= 'UTF-8')\n",
    "lst=[]\n",
    "count=0\n",
    "for i in pairs.readlines(): \n",
    "#     print(type(i))\n",
    "    count+=1\n",
    "    if(count>=2 and count%2!=0):\n",
    "        lst.append(eval(i.strip()))\n",
    "res=set()\n",
    "for i in range(50):\n",
    "#     print(lst[i][0])\n",
    "    res.add(lst[i][0])\n",
    "    res.add(lst[i][1])\n",
    "    \n",
    "for item in res:\n",
    "    print(item)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bf1845e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
